﻿<?xml version="1.0" encoding="utf-8" ?>
<AdoNetDataAccess connection="$ArticleDBConnection$" formatter="bizobjlist of Article(primaryPicture)">
	<FormatterInfo>
		<code for="vars" on="Article">
		int firstDepth = -1;
		</code>
		<code for="postEach" on="Article">
			<![CDATA[
			try
			{
				if(CurrentBizObj.get("mediaCacheName").ToString().Length==0)
					CurrentBizObj.put("mediaCacheName",info.get("defaultCacheName"));
			}
			catch(Exception e)
			{
			}
			 
			try
			{
				//The problem with the following 3 lines is that when we chunk the articles
				//we dont know what the real start dept is.  We need this to be a parameter into
				//the call so we know what the true start depth really is.
				//if(firstDepth == -1)
				//	firstDepth = (int)CurrentBizObj.get("depth");
				//CurrentBizObj.put("depth", (int)CurrentBizObj.get("depth") - firstDepth);						
			}
			catch(Exception e)
			{
			
			}
			
		]]></code>
		<code for="bizObjType" on="Article"><![CDATA[
			obj = App.bizObjMgr.create(info.get("BizObjType") != null?info.get("BizObjType").ToString():"Article");
		]]></code>
		<code for="bizObjListType" on="Article"><![CDATA[
			m_list = App.bizObjMgr.createList(info.get("BizObjType") != null?info.get("BizObjType").ToString():"Article");
		]]></code>
	</FormatterInfo>
	<Sql>
		<code>DetailLevel details = (DetailLevel)info.get("listDetailLevel");</code>
		SELECT ROW_NUMBER() OVER( 
			<code><![CDATA[
				string orderBy = "ORDER BY t.keyName ";
				string orderByStr = (string)info.get("orderBy");
				if(orderByStr != null && string.Compare("keyname", orderByStr, true) != 0)
				{
					if(string.Compare("treepos", orderByStr, true) == 0)
						orderBy = "ORDER BY t.positionName ";
					if(string.Compare("lastChildUpdated", orderByStr, true) == 0)
						orderBy = "ORDER BY nc.lastChildUpdated ";
				}
				if(info.get("sortDir")!= null)
					orderBy += info.get("sortDir").ToString();
				sql.Append(orderBy);
			]]></code>
			) AS rownum,
			<code><![CDATA[
				StringBuilder columns = new StringBuilder(1024);
				columns.Append("a.id,a.isSystem,");

				if(details > DetailLevel.idOnly)
					columns.Append("a.baseTitle,a.uniqueTitle,");
				if(details > DetailLevel.idAndTitle)
				{
					columns.Append(@"
						a.summary AS baseSummary, a.keyword, nc.dateUpdated AS counts_dateUpdated, nc.dateCreated AS counts_dateCreated, a.metaType,
						u.lastName AS counts_author_lastName, u.id AS counts_author_id, u.firstName AS counts_author_firstName, u.userName AS counts_author_userName, 
						u.dateCreated AS counts_author_dateCreated, a.id AS primaryPictureId,
						a.primaryMediaName AS primaryPictureName, a.primaryMediaName AS primaryPictureFileName, a.caption AS primaryPictureCaption, 
					");
					if(Cmn.IsTrue(info.get("includeTranslations"))) 
						columns.Append("a.translatedTitles,a.translatedSummaries,a.translationDetails,a.translatedCaptions,");
				}
				if(details > DetailLevel.basicDetails)
				{
					columns.Append("a.body AS baseBody,");
					if(true.Equals(info.get("includeTranslations")))
						columns.Append("a.translatedBodies,");
				}
			]]></code>
		<code><![CDATA[
			if(Cmn.IsTrue(info.get("includeECom")))
				columns.Append(@"
					ec.partId,ec.supplierPartId,ec.supplierId,
					ec.avgCostPrice,ec.listPrice,ec.sellPrice,ec.sellPriceDescription,
					ec.weight AS weightamount,weightUnit,
					ec.width AS [dimensions_width_amount],ec.widthUnit AS [dimensions_width_unit],
					ec.length AS [dimensions_length_amount],ec.lengthUnit AS [dimensions_length_unit],
					ec.depth AS [dimensions_depth_amount],ec.depthUnit AS [dimensions_depth_unit],
					ec.pricingId,");
			if(Cmn.IsTrue(info.get("includeCounts")))
				columns.Append(@"nc.totalL1 AS counts_totalL1, nc.totalL2 AS counts_totalL2, nc.totalL3 AS counts_totalL3,
					nc.totalL4 AS counts_totalL4, nc.totalViews AS counts_totalViews, nc.lastViewed AS counts_lastViewed,
					nc.lastChildUpdated AS counts_lastChildUpdated, lu.userName AS counts_lastChildUpdatedBy_userName, ");
					
			columns.Append("t.objectId, t.parentId, t.parentHostId, t.keyName, t.depth, t.rootId, t.treeName ");
			sql.Append(columns);
		]]></code>
		INTO
			#tmp_tbl_client	
		FROM            
			Articles AS a 
			LEFT OUTER JOIN	ObjectTreeNodes AS t ON a.id = t.objectId
			LEFT OUTER JOIN ObjectTreeCounts as nc on nc.objectId = a.id 
			<code><![CDATA[
			if(Cmn.IsTrue(info.get("includeCounts")))
				sql.Append(@" LEFT OUTER JOIN Users AS lu ON lu.id = nc.lastChildUpdatedByUserId ");
			if(details > DetailLevel.idAndTitle)
				sql.Append(@"
					LEFT OUTER JOIN	Users AS u ON u.id = nc.authorId 
				");
			]]></code>
		<code><![CDATA[
		if(true.Equals(info.get("includeECom")))
			sql.Append(@"LEFT OUTER JOIN eComProducts AS ec ON ec.id = a.id ");
		]]></code>
		WHERE
			<code>
				<![CDATA[
			bool addAnd = true;
			bool entireTree = Convert.ToBoolean(info.get("entireTree"));
			bool includeSelf = Convert.ToBoolean(info.get("includeSelf"));
			bool parentIdProvided = info.get("parentId") != null;
			if(parentIdProvided)
			{
				if(entireTree && includeSelf)
					sql.Append("t.parentHostId = @parentId");	
				else if(entireTree && !includeSelf)
					sql.Append("(t.parentHostId = @parentId AND t.id <> @parentId)");
				else if(!entireTree && includeSelf)
					sql.Append("(t.parentId = @parentId OR t.id = @parentId)");
				else if(!entireTree && !includeSelf)
					sql.Append("t.parentId = @parentId AND t.id <> @parentId");
			}
			else if(!entireTree)
				sql.Append("t.id = t.rootId");
			else
				addAnd = false;
			if(addAnd)
				sql.Append(" AND ");
			]]></code>

			<code>
				<![CDATA[
				int startDepth = info.get("startDepth") is int?(int)info.get("startDepth"):-1;
				int endDepth = info.get("endDepth") is int?(int)info.get("endDepth"):-1;
				if(startDepth != -1 && startDepth == endDepth)
					sql.Append("t.depth = ").Append(startDepth).Append(" AND ");
				else if(startDepth != -1 && endDepth != -1)
					sql.AppendFormat("t.depth >= {0} AND t.depth <= {1} AND ", startDepth, endDepth);
			]]></code>
		<code><![CDATA[
			bool hasCriteria = false;
			string firstPerc = "", secondPerc = "", cx="x";
			if(criteria != null && ((IBizObjList)criteria.get("filterOptions")).numElements != 0)
			{
				sql.Append("(");
				foreach(IBizObj filOp in criteria.get("filterOptions") as IBizObjList)
				{
					cx = filOp.get("criteria") as string;
					if (string.Compare(cx, "s", true) == 0)
						firstPerc = "%";
					else if (string.Compare(cx, "e", true) == 0)
						secondPerc = "%";
					else if (string.Compare(cx, "c", true) == 0)
						firstPerc = secondPerc = "%";
					if (firstPerc.Length > 0 || secondPerc.Length > 0)
						cx = "like";
					if(filOp.get("field") != null && (string)filOp.get("field") != "")
					{
						hasCriteria = true;
						sql.AppendFormat(" {0} {1} '{3}{2}{4}'", filOp.get("field"), cx, filOp.get("value"), firstPerc, secondPerc);
						sql.Append(criteria.get("filterIsAnd").Equals(true)?" AND":"  OR");
					}
				}
				if(hasCriteria)
				{
					sql.Length -= 4;
					sql.Append(") AND ");
				}
				else
					sql.Length -= 1;
			}
		]]></code>

		a.companyId = @companyId
		<code><![CDATA[
				if(info.get("objectTreeName") is string)
				{
					sql.Append(" AND t.treeName = @treeName ");
					addParameter(cmd,"objectTreeName","treeName","VarChar", info);
				}
				if(!parentIdProvided || !entireTree)
					sql.Append(" AND t.objectId = t.parentHostId ");

				sql.Append(orderBy);					
			]]></code>;
			
			CREATE UNIQUE CLUSTERED INDEX
				idx_uc_rownum
			ON
				#tmp_tbl_client(rownum)
				
			SELECT rownum, <code><![CDATA[
				string[] cols = columns.ToString().Split(',');
				columns = new StringBuilder(1024);
				foreach (string c in cols)
				{
					int p = c.IndexOf(" AS ");
					if(p != -1)
						columns.Append(c.Substring(p + 4));
					else if((p = c.IndexOf('.')) != -1)
						columns.Append(c.Substring(p+1));
					else 
						columns.Append(c);
					columns.Append(",");
				}
				string clmns = columns.ToString(0, columns.Length-1);
				sql.Append(clmns);
				]]></code>
			FROM
				#tmp_tbl_client
			WHERE
				rownum BETWEEN (@currentPage-1)*@pageSize+1 AND @currentPage*@pageSize
	</Sql>
	<Param name="parentId" type="UniqueIdentifier" />
	<Param name="companyId" type="UniqueIdentifier" />
	<Param name="pageSize" type="Integer" />
	<Param name="currentPage" type="Integer" />
</AdoNetDataAccess>
